package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.data.models.TInsuranceCheckModel;
import com.iris.live.services.data.repositories.CheckInsuranceRepositorySql;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by sallygo on 2017/5/23.
 */
@Repository
public class CheckInsuranceRepositorySqlImpl extends BaseRepository implements CheckInsuranceRepositorySql {
    private static Logger logger = LoggerFactory.getLogger(CheckInsuranceRepositorySqlImpl.class);

    @Override
    public void updateInsuranceCheckData(String permit,String importBatch){

            //更新实际值
            String sql = "UPDATE t_insurance_check a,  " +
                    "(  " +
                    "  SELECT  " +
                    "    c.`顾客编号` ,  " +
                    "    c.`车架号` ,  " +
                    "    a.`被保人姓名`,  " +
                    "    b.`交强险保单号`,  " +
                    "    b.`商业险保单号`,  " +
                    "    a.`交强险出单日期`,  " +
                    "    a.`商业险出单日期`,  " +
                    "    b.`交强险开始日期`,  " +
                    "    b.`交强险结束日期`,  " +
                    "    b.`商业险开始日期`,  " +
                    "    b.`商业险结束日期`,  " +
                    "    d.`交强险保险公司`,  " +
                    "    d.`商业险保险公司`,  " +
                    "    ROUND(SUM(IF(e.`险种`='交强险',e.`折后价`,0)),2) AS 交强险保费,  " +
                    "    ROUND(SUM(IF(e.`险种`!='交强险' AND e.`险种`!='车船税',IFNULL(e.`折后价`,0)+IFNULL(e.`不计免赔`,0),0)),2) AS 商业险保费,  " +
                    "    ROUND(IFNULL(d.`交强险手续费`,0),2) AS 交强险手续费,  " +
                    "    ROUND(IFNULL(d.`商业险手续费`,0),2) AS 商业险手续费  " +
                    "  FROM  " +
                    "    lsh保险申请附属 a  " +
                    "  LEFT JOIN(  " +
                    "    SELECT  " +
                    "      a.*  " +
                    "    FROM  " +
                    "      `lsh保险申请表附属_详细` a ,  " +
                    "      (  " +
                    "        SELECT  " +
                    "          max(`a`.`id`) AS `id`  " +
                    "        FROM  " +
                    "          `lsh保险申请表附属_详细` `a`  " +
                    "        GROUP BY  " +
                    "          `a`.`初始报价单号` ,  " +
                    "          `a`.`类型` ,  " +
                    "          a.`第N年`  " +
                    "      ) b  " +
                    "    WHERE  " +
                    "      a.id = b.id  " +
                    "  ) b ON a.`初始报价单号` = b.`初始报价单号` LEFT JOIN(  " +
                    "    SELECT a.*, c.`车架号` ,  " +
                    "    c.`生产号` FROM 报价单表 a ,  " +
                    "    (  " +
                    "      SELECT MAX(a.报价编号) 报价编号 FROM 报价单表 a GROUP BY a.`初始报价单号`  " +
                    "    ) b ,  " +
                    "    lsh库存表 c WHERE c.车架号 NOT LIKE 'IRIS%' AND a.`报价编号` = b.`报价编号` AND a.`库存编号` = c.`库存编号`  " +
                    "  ) c ON a.`初始报价单号` = c.`初始报价单号` LEFT JOIN 报价保险表 d ON c.`保险编号` = d.`保险编号` LEFT JOIN 报价保险详细表 e ON c.`保险编号` = e.`保险编号`   " +
                    "  WHERE c.`许可` = :permit AND a.`状态` = '06'  " +
                    "  AND (DATE_FORMAT(a.`交强险出单日期`,'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m') OR  " +
                    "       DATE_FORMAT(a.`商业险出单日期`,'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m'))  " +
                    "  GROUP BY c.`车架号`    " +
                    "    " +
                    "  UNION ALL  " +
                    "  " +
                    "  SELECT  " +
                    "    0 ,  " +
                    "    a.carnumber ,  " +
                    "    a.insName ,  " +
                    "    b.forced_number ,  " +
                    "    b.bus_number ,  " +
                    "    STR_TO_DATE(b.forced_op_time,'%Y-%m-%d %H:%i:%s'),  " +
                    "    STR_TO_DATE(b.bus_op_time,'%Y-%m-%d %H:%i:%s'),  " +
                    "    STR_TO_DATE(b.forced_insure_start,'%Y-%m-%d %H:%i:%s'),  " +
                    "    STR_TO_DATE(b.forced_insure_end,'%Y-%m-%d %H:%i:%s'),  " +
                    "    STR_TO_DATE(b.bus_insure_start,'%Y-%m-%d %H:%i:%s'),  " +
                    "    STR_TO_DATE(b.bus_insure_end,'%Y-%m-%d %H:%i:%s'),  " +
                    "    b.forced_ins_com,  " +
                    "    b.bus_ins_com,  " +
                    "    ROUND( IFNULL(b.forced_cost , 0) , 2),  " +
                    "    ROUND( IFNULL(b.bus_cost , 0) , 2),  " +
                    "    ROUND( IFNULL(b.forced_poundage , 0) , 2),  " +
                    "    ROUND( IFNULL(b.bus_poundage , 0) , 2)  " +
                    "  FROM  " +
                    "    (  " +
                    "      (  " +
                    "        `t_insurance_car_info` `a`  " +
                    "        LEFT JOIN `t_ins_buy` `b` ON((`a`.`carid` = `b`.`carid`))  " +
                    "      )  " +
                    "    )  " +
                    "  WHERE a.permit = :permit  " +
                    "  AND (DATE_FORMAT(STR_TO_DATE(b.forced_op_time,'%Y-%m-%d %H:%i:%s'),'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m')  " +
                    "    OR DATE_FORMAT(STR_TO_DATE(b.bus_op_time,'%Y-%m-%d %H:%i:%s'),'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m'))  " +
                    "  GROUP BY `a`.`carid`  " +
                    "  ) b  " +
                    "    SET     " +
                    "    `db_insured_cust_name` = b.`被保人姓名`,  " +
                    "    `db_TCI_policy_num` = b.`交强险保单号`,  " +
                    "    `db_VCI_policy_num` = b.`商业险保单号`,  " +
                    "    `db_TCI_invoice_date` = b.`交强险出单日期`,  " +
                    "    `db_VCI_invoice_date` = b.`商业险出单日期`,  " +
                    "    `db_TCI_effect_date` = b.`交强险开始日期`,  " +
                    "    `db_TCI_expiration_date` = b.`交强险结束日期`,  " +
                    "    `db_VCI_effect_date` = b.`商业险开始日期`,  " +
                    "    `db_VCI_expiration_date` = b.`商业险结束日期`,  " +
                    "    `db_TCI_company` = b.`交强险保险公司`,  " +
                    "    `db_VCI_company` = b.`交强险保险公司`,  " +
                    "    `db_TCI_price` = b.`交强险保费`,  " +
                    "    `db_VCI_price` = b.`商业险保费`,  " +
                    "    `db_TCI_fee` = b.`交强险手续费`,  " +
                    "    `db_VCI_fee` = b.`商业险手续费`  " +
                    "WHERE  " +
                    "  a.vin = b.车架号  " +
                    "AND a.import_batch = :batch  " +
                    "AND a.permit = :permit  " +
                    "  ";
            Map params = new HashMap();
            params.put("permit",permit);
            params.put("batch",importBatch);
            int result = this.executeUpdate(sql,params);
            logger.info("updateInsuranceCheckData更新成功{}条数据",result);
    }

    @Override
    public void insertInsuranceCheckData(String permit,String importBatch){
            //更新实际值
            String sql = "INSERT INTO t_insurance_check ( " +
                    " `import_batch` , " +
                    " `permit` , " +
                    " `vin` , " +
                    " `evaluation_user` , " +
                    " `db_insured_cust_name` , " +
                    " `db_TCI_policy_num` , " +
                    " `db_VCI_policy_num` , " +
                    " `db_TCI_invoice_date` , " +
                    " `db_VCI_invoice_date` , " +
                    " `db_TCI_effect_date` , " +
                    " `db_TCI_expiration_date` , " +
                    " `db_VCI_effect_date` , " +
                    " `db_VCI_expiration_date` , " +
                    " `db_TCI_company` , " +
                    " `db_VCI_company` , " +
                    " `db_TCI_price` , " +
                    " `db_VCI_price` , " +
                    " `db_TCI_fee` , " +
                    " `db_VCI_fee` , " +
                    " `compare_result`  " +
                    ") SELECT  " +
                    " DATE_FORMAT(CURDATE() , '%Y%m%d') , " +
                    " :permit , " +
                    " a.`车架号` , " +
                    " '保险专员' , " +
                    " a.`被保人姓名` , " +
                    " a.`交强险保单号` , " +
                    " a.`商业险保单号` , " +
                    " a.`交强险出单日期`, " +
                    " a.`商业险出单日期`, " +
                    " a.`交强险开始日期`, " +
                    " a.`交强险结束日期`, " +
                    " a.`商业险开始日期`, " +
                    " a.`商业险结束日期`, " +
                    " a.`交强险保险公司`, " +
                    " a.`商业险保险公司`, " +
                    " ROUND( IFNULL(a.`交强险保费` , 0) , 2) , " +
                    " ROUND( IFNULL(a.`商业险保费` , 0) , 2) , " +
                    " ROUND( IFNULL(a.`交强险手续费` , 0) , 2) , " +
                    " ROUND( IFNULL(a.`商业险手续费` , 0) , 2) , " +
                    " '03' " +
                    "FROM ( " +
                    "  SELECT " +
                    "   * " +
                    "  FROM " +
                    "   ( " +
                    "    SELECT " +
                    "       * " +
                    "      FROM " +
                    "       t_insurance_check a " +
                    "      WHERE " +
                    "       a.import_batch = :batch " +
                    "      AND a.permit = :permit " +
                    "     ) a " +
                    "    RIGHT JOIN( " +
                    "     SELECT " +
                    "    c.`顾客编号` , " +
                    "    c.`车架号` , " +
                    "    a.`被保人姓名`, " +
                    "    b.`交强险保单号`, " +
                    "    b.`商业险保单号`, " +
                    "    a.`交强险出单日期`, " +
                    "    a.`商业险出单日期`, " +
                    "    b.`交强险开始日期`, " +
                    "    b.`交强险结束日期`, " +
                    "    b.`商业险开始日期`, " +
                    "    b.`商业险结束日期`, " +
                    "    d.`交强险保险公司`, " +
                    "    d.`商业险保险公司`, " +
                    "    ROUND(SUM(IF(e.`险种`='交强险',e.`折后价`,0)),2) AS 交强险保费, " +
                    "    ROUND(SUM(IF(e.`险种`!='交强险' AND e.`险种`!='车船税',IFNULL(e.`折后价`,0)+IFNULL(e.`不计免赔`,0),0)),2) AS 商业险保费, " +
                    "    ROUND(IFNULL(d.`交强险手续费`,0),2) AS 交强险手续费, " +
                    "    ROUND(IFNULL(d.`商业险手续费`,0),2) AS 商业险手续费 " +
                    "   FROM " +
                    "    lsh保险申请附属 a " +
                    "   LEFT JOIN( " +
                    "    SELECT " +
                    "     a.* " +
                    "    FROM " +
                    "     `lsh保险申请表附属_详细` a , " +
                    "     ( " +
                    "      SELECT " +
                    "       max(`a`.`id`) AS `id` " +
                    "      FROM " +
                    "       `lsh保险申请表附属_详细` `a` " +
                    "      GROUP BY " +
                    "       `a`.`初始报价单号` , " +
                    "       `a`.`类型` , " +
                    "       a.`第N年` " +
                    "     ) b " +
                    "    WHERE " +
                    "     a.id = b.id " +
                    "   ) b ON a.`初始报价单号` = b.`初始报价单号` LEFT JOIN( " +
                    "    SELECT a.*, c.`车架号` , " +
                    "    c.`生产号` FROM 报价单表 a , " +
                    "    ( " +
                    "     SELECT MAX(a.报价编号) 报价编号 FROM 报价单表 a GROUP BY a.`初始报价单号` " +
                    "    ) b , " +
                    "    lsh库存表 c WHERE c.车架号 NOT LIKE 'IRIS%' AND a.`报价编号` = b.`报价编号` AND a.`库存编号` = c.`库存编号` " +
                    "   ) c ON a.`初始报价单号` = c.`初始报价单号` LEFT JOIN 报价保险表 d ON c.`保险编号` = d.`保险编号` LEFT JOIN 报价保险详细表 e ON c.`保险编号` = e.`保险编号`  " +
                    "   WHERE c.`许可` = :permit AND a.`状态` = '06' " +
                    "   AND (DATE_FORMAT(a.`交强险出单日期`,'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m') OR " +
                    "        DATE_FORMAT(a.`商业险出单日期`,'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m')) " +
                    "   GROUP BY c.`车架号`  " +
                    "    " +
                    "   UNION ALL " +
                    " " +
                    "   SELECT " +
                    "    0 , " +
                    "    a.carnumber , " +
                    "    a.insName , " +
                    "    b.forced_number , " +
                    "    b.bus_number , " +
                    "    STR_TO_DATE(b.forced_op_time,'%Y-%m-%d %H:%i:%s'), " +
                    "    STR_TO_DATE(b.bus_op_time,'%Y-%m-%d %H:%i:%s'), " +
                    "    STR_TO_DATE(b.forced_insure_start,'%Y-%m-%d %H:%i:%s'), " +
                    "    STR_TO_DATE(b.forced_insure_end,'%Y-%m-%d %H:%i:%s'), " +
                    "    STR_TO_DATE(b.bus_insure_start,'%Y-%m-%d %H:%i:%s'), " +
                    "    STR_TO_DATE(b.bus_insure_end,'%Y-%m-%d %H:%i:%s'), " +
                    "    b.forced_ins_com, " +
                    "    b.bus_ins_com, " +
                    "    ROUND( IFNULL(b.forced_cost , 0) , 2), " +
                    "    ROUND( IFNULL(b.bus_cost , 0) , 2), " +
                    "    ROUND( IFNULL(b.forced_poundage , 0) , 2), " +
                    "    ROUND( IFNULL(b.bus_poundage , 0) , 2) " +
                    "   FROM " +
                    "    ( " +
                    "     ( " +
                    "      `t_insurance_car_info` `a` " +
                    "      LEFT JOIN `t_ins_buy` `b` ON((`a`.`carid` = `b`.`carid`)) " +
                    "     ) " +
                    "    ) " +
                    "   WHERE a.permit = :permit " +
                    "   AND (DATE_FORMAT(STR_TO_DATE(b.forced_op_time,'%Y-%m-%d %H:%i:%s'),'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m') " +
                    "    OR DATE_FORMAT(STR_TO_DATE(b.bus_op_time,'%Y-%m-%d %H:%i:%s'),'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m')) " +
                    "   GROUP BY `a`.`carid` " +
                    " " +
                    "  ) b ON a.vin = b.车架号 WHERE ISNULL(a.vin) " +
                    " ) a";
            Map params = new HashMap();
            params.put("permit",permit);
            params.put("batch",importBatch);
            int result = this.executeUpdate(sql,params);
            logger.info("updateInsuranceCheckData更新成功{}条数据",result);
    }

    public List<TInsuranceCheckModel> selectInsuranceCheckData(String permit, String importBatch){
        String sql = "select * from t_insurance_check a where a.import_batch = :batch and a.permit = :permit ";
        Map params = new HashMap();
        params.put("permit",permit);
        params.put("batch",importBatch);
        List<TInsuranceCheckModel> reList = this.getList(sql,params,TInsuranceCheckModel.class);
        return reList;
    }

    public List<TInsuranceCheckModel> selectDBInsuranceCheckData(String permit,String importBatch){
        {
            String sql = "SELECT " +
                    " import_batch , " +
                    " permit , " +
                    " vin , " +
                    " evaluation_user , " +
                    " db_insured_cust_name , " +
                    " " +
                    "IF( " +
                    " INSTR(db_TCI_policy_num , ',') > 0 , " +
                    " LEFT( " +
                    "  db_TCI_policy_num , " +
                    "  INSTR(db_TCI_policy_num , ',') - 1 " +
                    " ) , " +
                    " db_TCI_policy_num " +
                    ") db_TCI_policy_num , " +
                    " " +
                    "IF( " +
                    " INSTR(db_VCI_policy_num , ',') > 0 , " +
                    " LEFT( " +
                    "  db_VCI_policy_num , " +
                    "  INSTR(db_VCI_policy_num , ',') - 1 " +
                    " ) , " +
                    " db_VCI_policy_num " +
                    ") db_VCI_policy_num , " +
                    " db_TCI_invoice_date , " +
                    " db_VCI_invoice_date , " +
                    " " +
                    "IF( " +
                    " INSTR(db_TCI_effect_date , ',') > 0 , " +
                    " LEFT( " +
                    "  db_TCI_effect_date , " +
                    "  INSTR(db_TCI_effect_date , ',') - 1 " +
                    " ) , " +
                    " db_TCI_effect_date " +
                    ") db_TCI_effect_date , " +
                    " " +
                    "IF( " +
                    " INSTR(db_TCI_expiration_date , ',') > 0 , " +
                    " LEFT( " +
                    "  db_TCI_expiration_date , " +
                    "  INSTR(db_TCI_expiration_date , ',') - 1 " +
                    " ) , " +
                    " db_TCI_expiration_date " +
                    ") db_TCI_expiration_date , " +
                    " " +
                    "IF( " +
                    " INSTR(db_VCI_effect_date , ',') > 0 , " +
                    " LEFT( " +
                    "  db_VCI_effect_date , " +
                    "  INSTR(db_VCI_effect_date , ',') - 1 " +
                    " ) , " +
                    " db_VCI_effect_date " +
                    ") db_VCI_effect_date , " +
                    " " +
                    "IF( " +
                    " INSTR(db_VCI_expiration_date , ',') > 0 , " +
                    " LEFT( " +
                    "  db_VCI_expiration_date , " +
                    "  INSTR(db_VCI_expiration_date , ',') - 1 " +
                    " ) , " +
                    " db_VCI_expiration_date " +
                    ") db_VCI_expiration_date , " +
                    " db_TCI_company , " +
                    " db_VCI_company , " +
                    " db_TCI_price , " +
                    " db_VCI_price , " +
                    " db_TCI_fee , " +
                    " db_VCI_fee " +
                    "FROM " +
                    " ( " +
                    "  SELECT " +
                    "   import_batch , " +
                    "   permit , " +
                    "   vin , " +
                    "   evaluation_user , " +
                    "   db_insured_cust_name , " +
                    "   GROUP_CONCAT(db_TCI_policy_num) db_TCI_policy_num , " +
                    "   GROUP_CONCAT(db_VCI_policy_num) db_VCI_policy_num , " +
                    "   db_TCI_invoice_date , " +
                    "   db_VCI_invoice_date , " +
                    "   GROUP_CONCAT(db_TCI_effect_date) db_TCI_effect_date , " +
                    "   GROUP_CONCAT(db_TCI_expiration_date) db_TCI_expiration_date , " +
                    "   GROUP_CONCAT(db_VCI_effect_date) db_VCI_effect_date , " +
                    "   GROUP_CONCAT(db_VCI_expiration_date) db_VCI_expiration_date , " +
                    "   db_TCI_company , " +
                    "   db_VCI_company , " +
                    "   db_TCI_price , " +
                    "   db_VCI_price , " +
                    "   db_TCI_fee , " +
                    "   db_VCI_fee FROM( " +
                    "    SELECT :batch import_batch , " +
                    "    c.`许可` permit , " +
                    "    c.`车架号` vin , " +
                    "    '保险专员' `evaluation_user` , " +
                    "    a.`被保人姓名` db_insured_cust_name , " +
                    "    CASE WHEN b.`第N年`=1 THEN b.`交强险保单号` END db_TCI_policy_num , " +
                    "    CASE WHEN b.`第N年`=1 THEN b.`商业险保单号` END db_VCI_policy_num , " +
                    "    a.`交强险出单日期` db_TCI_invoice_date , " +
                    "    a.`商业险出单日期` db_VCI_invoice_date , " +
                    "    MIN(b.`交强险开始日期`) db_TCI_effect_date , " +
                    "    MAX(b.`交强险结束日期`) db_TCI_expiration_date , " +
                    "    MIN(b.`商业险开始日期`) db_VCI_effect_date , " +
                    "    MAX(b.`商业险结束日期`) db_VCI_expiration_date , " +
                    "    d.`交强险保险公司` db_TCI_company , " +
                    "    d.`商业险保险公司` db_VCI_company , " +
                    "    ROUND( " +
                    "     SUM( " +
                    "      IF( " +
                    "       e.`险种` = '交强险' AND (b.`第N年`=1 OR ISNULL(b.id)) , " +
                    "       e.`折后价` , " +
                    "       0 " +
                    "      ) " +
                    "     ) , " +
                    "     2 " +
                    "    ) AS db_TCI_price , " +
                    "    ROUND( " +
                    "     SUM( " +
                    "      IF( " +
                    "       e.`险种` != '交强险' AND e.`险种` != '车船税' AND (b.`第N年`=1 OR ISNULL(b.id)) , " +
                    "       IFNULL(e.`折后价` , 0) + IFNULL(e.`不计免赔` , 0) , " +
                    "       0 " +
                    "      ) " +
                    "     ) , " +
                    "     2 " +
                    "    ) AS db_VCI_price , " +
                    "    ROUND(d.`交强险手续费` , 2) AS db_TCI_fee , " +
                    "    ROUND(d.`商业险手续费` , 2) AS db_VCI_fee FROM lsh保险申请附属 a LEFT JOIN( " +
                    "     SELECT a.* FROM `lsh保险申请表附属_详细` a , " +
                    "     ( " +
                    "      SELECT max(`a`.`id`) AS `id` FROM `lsh保险申请表附属_详细` `a` GROUP BY `a`.`初始报价单号` , " +
                    "      `a`.`类型` , " +
                    "      `a`.`第N年` " +
                    "     ) b WHERE a.id = b.id " +
                    "    ) b ON a.`初始报价单号` = b.`初始报价单号` LEFT JOIN( " +
                    "     SELECT a.*, c.`车架号` , " +
                    "     c.`生产号` FROM 报价单表 a , " +
                    "     ( " +
                    "      SELECT MAX(a.报价编号) 报价编号 FROM 报价单表 a GROUP BY a.`初始报价单号` " +
                    "     ) b , " +
                    "     lsh库存表 c WHERE c.车架号 NOT LIKE 'IRIS%' AND a.`报价编号` = b.`报价编号` AND a.`库存编号` = c.`库存编号` " +
                    "    ) c ON a.`初始报价单号` = c.`初始报价单号` LEFT JOIN 报价保险表 d ON c.`保险编号` = d.`保险编号` LEFT JOIN 报价保险详细表 e ON c.`保险编号` = e.`保险编号` WHERE c.`许可` = :permit AND a.`状态` = '06' AND( " +
                    "     DATE_FORMAT( " +
                    "      a.`交强险出单日期` , " +
                    "      '%y-%m' " +
                    "     ) = DATE_FORMAT(CURDATE() , '%y-%m') OR DATE_FORMAT( " +
                    "      a.`商业险出单日期` , " +
                    "      '%y-%m' " +
                    "     ) = DATE_FORMAT(CURDATE() , '%y-%m') " +
                    "    ) GROUP BY c.`车架号` , " +
                    "    b.`类型` " +
                    "   ) a GROUP BY a.`vin` UNION ALL" +
                    " SELECT :batch import_batch , " +
                    "   a.permit permit , " +
                    "   a.VIN , " +
                    "   '保险专员' `evaluation_user` , " +
                    "   a.insName , " +
                    "   b.forced_number , " +
                    "   b.bus_number , " +
                    "   STR_TO_DATE(b.forced_op_time , '%Y-%m-%d') , " +
                    "   STR_TO_DATE(b.bus_op_time , '%Y-%m-%d') , " +
                    "   STR_TO_DATE( " +
                    "    b.forced_insure_start , " +
                    "    '%Y-%m-%d' " +
                    "   ) , " +
                    "   STR_TO_DATE(b.forced_insure_end , '%Y-%m-%d') , " +
                    "   STR_TO_DATE(b.bus_insure_start , '%Y-%m-%d') , " +
                    "   STR_TO_DATE(b.bus_insure_end , '%Y-%m-%d') , " +
                    "   b.forced_ins_com , " +
                    "   b.bus_ins_com , " +
                    "   ROUND(b.forced_cost , 2) , " +
                    "   ROUND(b.bus_cost , 2) , " +
                    "   ROUND(b.forced_poundage , 2) , " +
                    "   ROUND(b.bus_poundage , 2) FROM( " +
                    "    ( " +
                    "     `t_insurance_car_info` `a` LEFT JOIN `t_ins_buy` `b` ON((`a`.`carid` = `b`.`carid`)) " +
                    "    ) " +
                    "   ) WHERE a.permit = :permit AND( " +
                    "    DATE_FORMAT( " +
                    "     STR_TO_DATE(b.forced_op_time , '%Y-%m-%d') , " +
                    "     '%y-%m' " +
                    "    ) = DATE_FORMAT(CURDATE() , '%y-%m') OR DATE_FORMAT( " +
                    "     STR_TO_DATE(b.bus_op_time , '%Y-%m-%d') , " +
                    "     '%y-%m' " +
                    "    ) = DATE_FORMAT(CURDATE() , '%y-%m') " +
                    "   ) GROUP BY `a`.`carid` " +
                    " ) aa";
            Map params = new HashMap();
            params.put("permit",permit);
            params.put("batch",importBatch);
            List<TInsuranceCheckModel> reList = this.getList(sql,params);
            return reList;
        }
    }

}
